﻿using Nova.Data;
using Nova_MOD;
using Nova_MOD.ResultModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Nova_DAL
{
    public class TBase_StorageDal : BaseRepository<TBase_Storage>
    {
        public List<AutoOutStockModel> GetAutoOutStockList(string matCode, string serialNum)
        {
            string sqlStr = string.Format(@"SELECT TRP.Tray_ID,TBP.Pos_ID,TBM.Mat_Code,TBM.Mat_Name,
                        TBS.Mat_Num,TBS.PID,TBI.TS01,TBP.Pos_State,TBS.CDT
                        From TBase_Storage TBS  LEFT JOIN TRel_TrayPosContainer TRT
                        ON TBS.ContainerID = TRT.ContainerID
                        LEFT JOIN TBase_TrayPos TBT ON TBT.Relations_ID = TRT.Relations_ID
                        LEFT JOIN TRelations TRP ON TRP.Tray_ID = TBT.Tray_ID
                        LEFT JOIN TBase_Position TBP ON TRP.Pos_ID = TBP.Pos_ID
                        LEFT JOIN TBase_Zone TBZ ON TBZ.Zone_ID = TBP.Zone_ID
                        LEFT JOIN TBase_InterFace TBI ON TBI.Sec_Mat_ID = TBS.Sec_Mat_ID
                        LEFT JOIN TBase_Material TBM ON TBM.Mat_Code = TBI.Mat_Code
                        Where TRP.Pos_ID !='200000000000' and TBP.Pos_State IN(1,4)  and 
                        TBM.Mat_Code = '{0}' AND TBI.TS01='{1}' ORDER BY TBS.CDT ASC", matCode, serialNum);
            return GetByConditionList<AutoOutStockModel>(sqlStr);
        }
        public int GetMetarialTypeCount(string trayId)
        {
            string sqlStr = string.Format(@"SELECT TBS.* FROM dbo.TBase_Storage TBS
                        INNER JOIN dbo.TRel_TrayPosContainer TTP
                        ON TBS.ContainerID = TTP.ContainerID INNER JOIN dbo.TBase_TrayPos TBT
                        ON TTP.Relations_ID = TBT.Relations_ID WHERE TBT.Tray_ID='{0}'", trayId);
            var list = GetByConditionList<TBase_Storage>(sqlStr);
            return list.Count;
        }

        public List<StorageStaticModel> GetStorageStaticList()
        {
            string sqlStr = @"SELECT TBM.Mat_Code,TBM.Mat_Name, ISNULL(TBM.MiniTypeID,0) MatType,TBM.S01,
                        SUM(TBS.Mat_Num) MatNum FROM dbo.TBase_Storage TBS LEFT JOIN dbo.TBase_Interface TBI 
                        ON TBS.Sec_Mat_ID = TBI.Sec_Mat_ID LEFT JOIN dbo.TBase_Material TBM 
                        ON TBI.Mat_Code = TBM.Mat_Code GROUP BY TBM.Mat_Code,TBM.Mat_Name,TBM.MiniTypeID,TBM.S01";
            return GetByConditionList<StorageStaticModel>(sqlStr);
        }
    }
}
